<?php
class m_adik_asuh extends Model{
    var $table1 = "adik_asuh",
        $table2 = "dinamis_adik_asuh",
        $table3 = "keuangan_adik_asuh",    
        $gallery_path,
        $thumb_path;
    function m_adik_asuh(){
        parent::Model();
        $this->gallery_path = realpath(APPPATH.'../image/foto_adik_asuh');
        $this->thumb_path = $this->gallery_path.'/thumbs';
    }    
    function getCountRows($params){
        if($params['nama'] != NULL){
            $action = "and a.nama like ('%$params[nama]%')";
        }
        $query = $this->db->query("select a.*,da.alamat,da.no_telp,da.jumlah_saudara,s.nama as sekolah,k.nama as kelas,t.nama as tingkat_pendidikan,ta.nama as tahun_ajaran,sm.nama as semester from adik_asuh a
                                   join dinamis_adik_asuh da on a.id=da.id_adik_asuh 
                                   join sekolah s on da.id_sekolah=s.id 
                                   join kelas k on da.id_kelas=k.id 
                                   join tingkat_pendidikan t on k.id_tingkat_pendidikan=t.id 
                                   join tahun_ajaran ta on da.id_tahun_ajaran = ta.id
                                   join semester sm on ta.id_semester = sm.id
                                   where a.status = '1' and da.status='1' $action");
        
        return $query->num_rows();
    }
    function getListAdikAsuh($offset = NULL,$limit = NULL,$params,$sortBy = NULL,$type = NULL){
        if($params['nama'] != NULL){
            $action = "and a.nama like ('%$params[nama]%')";
        }
        if($sortBy == 'nama'){
            $order = "order by a.nama $type";
        }else if($sortBy == 'tgl_lahir'){
            $order = "order by a.tgl_lahir $type";
        }else $order = 'order by a.nama asc';
        $query = $this->db->query("select a.*,da.alamat,da.no_telp,da.jumlah_saudara,s.nama as sekolah,k.nama as kelas,t.nama as tingkat_pendidikan,ta.nama as tahun_ajaran,sm.nama as semester from adik_asuh a
                                   join dinamis_adik_asuh da on a.id=da.id_adik_asuh 
                                   join sekolah s on da.id_sekolah=s.id 
                                   join kelas k on da.id_kelas=k.id 
                                   join tingkat_pendidikan t on k.id_tingkat_pendidikan=t.id 
                                   join tahun_ajaran ta on da.id_tahun_ajaran = ta.id
                                   join semester sm on ta.id_semester = sm.id
                                   where a.status = '1' and da.status='1' $action $order limit $offset,$limit");
        if($query->num_rows() > 0){
            $result = $query->result_array();
        }else $result = array();
        
        return $result;
    }
    function getAdikAsuhTanpaDonatur(){
        $query = $this->db->query("select a.*,da.alamat,da.no_telp,s.id as id_sekolah,k.id as id_kelas,ta.id as id_tahun_ajaran,da.jumlah_saudara,s.nama as sekolah,k.nama as kelas,t.nama as tingkat_pendidikan,ta.nama as tahun_ajaran,sm.nama as semester,t.id as id_tingkat_pendidikan from adik_asuh a
                                   join dinamis_adik_asuh da on a.id=da.id_adik_asuh 
                                   join sekolah s on da.id_sekolah=s.id 
                                   join kelas k on da.id_kelas=k.id 
                                   join tingkat_pendidikan t on k.id_tingkat_pendidikan=t.id 
                                   join tahun_ajaran ta on da.id_tahun_ajaran = ta.id
                                   join semester sm on ta.id_semester = sm.id
                                   where a.status = '1' and da.status='1' and a.id not in (select id_adik_asuh from relasi_donatur_tunggal)");
        if($query->num_rows() > 0){
            $result = $query->result_array();
        }else $result = array();
        
        return $result;
    }
    function getAdikAsuhByTingkatPendidikan($idTingkatPendidikan){
        $query = $this->db->query("select a.*,da.alamat,da.no_telp,da.jumlah_saudara,s.nama as sekolah,k.nama as kelas,t.nama as tingkat_pendidikan,ta.nama as tahun_ajaran,sm.nama as semester from adik_asuh a
                                   join dinamis_adik_asuh da on a.id=da.id_adik_asuh 
                                   join sekolah s on da.id_sekolah=s.id 
                                   join kelas k on da.id_kelas=k.id 
                                   join tingkat_pendidikan t on k.id_tingkat_pendidikan=t.id 
                                   join tahun_ajaran ta on da.id_tahun_ajaran = ta.id
                                   join semester sm on ta.id_semester = sm.id
                                   where a.status = '1' and da.status='1' and t.id = '$idTingkatPendidikan'");
        if($query->num_rows() > 0){
            $result = $query->result_array();
        }else $result = array();
        
        return $result;
    }
    function getAdikAsuhById($id){
        $query = $this->db->query("select a.*,da.alamat,da.no_telp,da.jumlah_saudara,da.gambar,da.id_sekolah,da.id_kelas,s.nama as sekolah,k.nama as kelas,t.nama as tingkat_pendidikan,ta.nama as tahun_ajaran,ta.id as id_tahun_ajaran,sm.nama as semester from adik_asuh a
                                   join dinamis_adik_asuh da on a.id=da.id_adik_asuh 
                                   join sekolah s on da.id_sekolah=s.id 
                                   join kelas k on da.id_kelas=k.id 
                                   join tingkat_pendidikan t on k.id_tingkat_pendidikan=t.id 
                                   join tahun_ajaran ta on da.id_tahun_ajaran = ta.id
                                   join semester sm on ta.id_semester = sm.id
                                   where da.status='1' and a.id='$id'");
        if($query->num_rows() > 0){
            $result = $query->result_array();
        }else $result = array();
        
        return $result;
    }
    function getDinamisAdikAsuh($id = NULL){
        if($id != NULL){
          $where = "where da.id_adik_asuh='$id'";    
        }else $where = "";
        
        $query = $this->db->query('select da.*,s.nama as sekolah,k.nama as kelas,t.nama as tingkat_pendidikan,ta.nama as tahun_ajaran,sm.nama as semester from dinamis_adik_asuh da
                                  join sekolah s on da.id_sekolah = s.id 
                                  join kelas k on da.id_kelas = k.id 
                                  join tingkat_pendidikan t on k.id_tingkat_pendidikan = t.id 
                                  join tahun_ajaran ta on da.id_tahun_ajaran = ta.id
                                  join semester sm on ta.id_semester = sm.id
                                  '.$where.' order by status desc');
        if($query->num_rows() > 0){
            $result = $query->result_array();
        }else $result = array();
        
        return $result;
    }
    function addAdikAsuh($data){
       $nama = $data['nama'];
       $tglLahir = date2mysql($data['tglLahir']);
       $jenKel = $data['jeKel'];
       $ortu = $data['ortu'];
       $jumSaudara = $data['jumSaudara'];
       $alamat = $data['alamat'];
       $noTelp = $data['noTelp'];
       $sekolah = $data['idSekolah'];
       $kelas = $data['kelas'];
       $idTahunAjaran = $data['id_tahun_ajaran'];
       $tanggal = date('Y-m-d');
       $tglDiterima = date2mysql($data['tglDiterima']);
       $status = '1';
       
       $dataGambar = $this->uploadValue($this->gallery_path);
       $this->makeThumbnail($dataGambar['full_path'],$dataGambar['image_height'],$dataGambar['image_width'],$this->thumb_path);
       
       $adikAsuh = array('nama' => $nama,
           'tgl_lahir' => $tglLahir,
           'jenis_kelamin' => $jenKel,
           'ortu' => $ortu,
           'tgl_diterima' => $tglDiterima,
           'status' => $status);
       $this->db->insert($this->table1,$adikAsuh);
       $id = $this->db->insert_id();
       
       $dinamisAdikAsuh = array('tanggal' => $tanggal,
           'id_adik_asuh' => $id,
           'alamat' => $alamat,
           'no_telp' => $noTelp,
           'jumlah_saudara' => $jumSaudara,
           'id_sekolah' => $sekolah,
           'id_kelas' => $kelas,
           'id_tahun_ajaran' => $idTahunAjaran,
           'gambar' => $dataGambar['file_name'],
           'status' => '1');
       $insert = $this->db->insert($this->table2,$dinamisAdikAsuh);
       if(!$insert){
           return FALSE;
       }else return TRUE;
    }
    function editAdikAsuh($data){
       $id = $data['id']; 
       $nama = $data['nama'];
       $tglLahir = date2mysql($data['tglLahir']);
       $jenKel = $data['jeKel'];
       $ortu = $data['ortu'];
       $jumSaudara = $data['jumSaudara'];
       $alamat = $data['alamat'];
       $noTelp = $data['noTelp'];
       $sekolah = $data['idSekolah'];
       $idTahunAjaran = $data['id_tahun_ajaran'];
       $kelas = $data['kelas'];
       $tglDiterima = date2mysql($data['tglDiterima']);
       
       $dataGambar = $this->uploadValue($this->gallery_path);
       $this->makeThumbnail($dataGambar['full_path'],$dataGambar['image_height'],$dataGambar['image_width'],$this->thumb_path);
       
       $adikAsuh = array('nama' => $nama,
           'tgl_lahir' => $tglLahir,
           'jenis_kelamin' => $jenKel,
           'ortu' => $ortu,
           'tgl_diterima' => $tglDiterima);
       
       $this->db->where('id',$id);
       $this->db->update($this->table1,$adikAsuh);
       
       $dinamisAdikAsuh['alamat'] = $alamat;
       $dinamisAdikAsuh['no_telp'] = $noTelp;
       $dinamisAdikAsuh['jumlah_saudara'] = $jumSaudara;
       $dinamisAdikAsuh['id_sekolah'] = $sekolah;
       $dinamisAdikAsuh['id_kelas'] = $kelas;
       $dinamisAdikAsuh['id_tahun_ajaran'] = $idTahunAjaran;
       $dinamisAdikAsuh['alamat'] = $alamat;
       if($dataGambar['file_name'] != ''){
           $dinamisAdikAsuh['gambar'] = $dataGambar['file_name'];
       }
       
       $this->db->where('id_adik_asuh',$id);
       $this->db->where('status','1');
       $update = $this->db->update($this->table2,$dinamisAdikAsuh);
       
       if(!$update){
           return FALSE;
       }else return TRUE;
    }
    function addDinamisAdikAsuh($data){
        $dataGambar = $this->uploadValue($this->gallery_path);
        $this->makeThumbnail($dataGambar['full_path'],$dataGambar['image_height'],$dataGambar['image_width'],$this->thumb_path);
        
        $data['gambar'] = $dataGambar['file_name'];
        
        $this->db->where('id_adik_asuh',$data['id_adik_asuh']);
        $this->db->update($this->table2,array('status' => '0'));
        
        $insert = $this->db->insert($this->table2,$data);
        if(!$insert){
            return FALSE;
        }else return TRUE;
    }
    function deleteAdikAsuh($id){
        $delete = $this->db->delete($this->table1,array('id'=>$id));
        if(!$delete){
            return FALSE;
        }else return TRUE;
    }
    function getAdikAsuhByMember($idMember,$nama){
        $query = $this->db->query("select a.nama as adik_asuh,a.id as id_adik_asuh,k.id as id_kelas,k.nama as kelas,ta.id as id_tahun_ajaran,ta.nama as tahun_ajaran,sms.nama as semester,da.alamat,s.nama as sekolah,s.id as id_sekolah from relasi_pembina rp 
                          join adik_asuh a on rp.id_adik_asuh = a.id
                          join dinamis_adik_asuh da on da.id_adik_asuh = a.id 
                          join sekolah s on da.id_sekolah = s.id 
                          join kelas k on da.id_kelas = k.id
                          join tahun_ajaran ta on da.id_tahun_ajaran = ta.id
                          join semester sms on ta.id_semester = sms.id
                          where a.status = '1' and da.status = '1' and rp.id_member = '$idMember' and locate('$nama',a.nama) > 0 order by locate ('$nama',a.nama)");
        if($query->num_rows() > 0){
            $result =  $query->result_array();
        }else $result =  array();
        
        return $result;
    }
    function searchAdikAsuh($nama){
        $query = $this->db->query("select a.*,da.alamat,da.no_telp,da.jumlah_saudara,s.nama as sekolah,s.id as id_sekolah,k.id as id_kelas,k.nama as kelas,t.nama as tingkat_pendidikan,ta.id as id_tahun_ajaran,ta.nama as tahun_ajaran,sm.nama as semester from adik_asuh a
                                   join dinamis_adik_asuh da on a.id=da.id_adik_asuh 
                                   join sekolah s on da.id_sekolah=s.id 
                                   join kelas k on da.id_kelas=k.id 
                                   join tingkat_pendidikan t on k.id_tingkat_pendidikan=t.id 
                                   join tahun_ajaran ta on da.id_tahun_ajaran = ta.id
                                   join semester sm on ta.id_semester = sm.id
                                   where a.status = '1' and da.status='1' and locate('$nama',a.nama) > 0 order by locate ('$nama',a.nama)");
        if($query->num_rows() > 0){
            $result =  $query->result_array();
        }else $result =  array();
        
        return $result;
    }
    function getTotalDebet($idAdikAsuh = NULL){
        if($idAdikAsuh != NULL){
            $where = "where id_adik_asuh='$idAdikAsuh'";
        }else $where = "";
        
        $totalDebet = $this->db->query("select SUM(debet) as total_debet from keuangan_adik_asuh $where");
        return $totalDebet->result_array();
    }
    function getTotalKredit($idAdikAsuh = NULL){
        if($idAdikAsuh != NULL){
            $where = "where id_adik_asuh='$idAdikAsuh'";
        }else $where = "";
        
        $totalKredit = $this->db->query("select SUM(kredit) as total_kredit from keuangan_adik_asuh $where");
        return $totalKredit->result_array();
    }
    function addPengeluaranAdikAsuh($data){
        $insert = $this->db->insert($this->table3,$data);
        $id = $this->db->insert_id();
        if($insert){
            $return['status'] = TRUE;
            $return['id'] = $id;
        }else{
            $return['status'] = FALSE;
            $return['id'] = '';
        }
        return $return;
    }
    function getListPengeluaranAdikAsuh($id = NULL){
        if($id != NULL){
            $where = "and k.id = '$id'";
        }else $where = "";
        
        $query = $this->db->query("select k.tanggal,k.kredit,k.keterangan,m.nama as member,da.alamat,a.nama as adik_asuh,s.nama as sekolah,kls.nama as kelas,ta.nama as tahun_ajaran,sms.nama as semester from keuangan_adik_asuh k
                                  join member m on k.id_petugas = m.id
                                  join adik_asuh a on k.id_adik_asuh = a.id
                                  join kelas kls on k.id_kelas = kls.id
                                  join tahun_ajaran ta on k.id_tahun_ajaran = ta.id
                                  join semester sms on ta.id_semester = sms.id
                                  join dinamis_adik_asuh da on da.id_adik_asuh = a.id
                                  join sekolah s on k.id_sekolah = s.id
                                  where a.status = '1' and da.status = '1' $where");
        if($query->num_rows()>0){
            $return = $query->result_array();
        }else $return = array();
        
        return $return;
    }
    function getRaport($idAdikAsuh = NULL,$idTingkatPendidikan = NULL){
        $query = $this->db->query("select r.id as id_raport,r.tanggal,s.nama as sekolah,ta.nama as tahun_ajaran,sms.nama as semester,m.nama as member,tp.nama as tingkat_pendidikan,kls.nama as kelas 
                from raport r 
                join kelas kls on r.id_kelas = kls.id 
                join sekolah s on r.id_sekolah = s.id
                join tahun_ajaran ta on r.id_tahun_ajaran = ta.id
                join semester sms on ta.id_semester = sms.id
                join member m on r.id_member = m.id
                join tingkat_pendidikan tp on kls.id_tingkat_pendidikan = tp.id 
                where r.id_adik_asuh = '$idAdikAsuh' and tp.id = '$idTingkatPendidikan' order by kls.id");
       
        if($query->num_rows()>0){
            $return = $query->result_array();
        }else $return = array();
        
        return $return; 
    }
    /*
     * mengembalikan data file yg diupload
     */
    function uploadValue($gallery_path){
        $config['upload_path'] = $this->gallery_path;
        $config['allowed_types'] = 'gif|jpg|png';
        $config['max_size']	= '2000';
        
        $this->upload->initialize($config);
        $this->upload->do_upload();
        
        return $dataGambar = $this->upload->data();
    }
    //--------------------------------------------------------------------------
    /*
     * membuat gambar thumbnail pada file yg diupload
     */
    function makeThumbnail($fullPath,$imageHeight,$imageWidth,$thumbPath){
       $widthThumb = 150;
       $heightThumb = ceil(($widthThumb * $imageHeight)/$imageWidth);
       
       $config = array(
       'source_image'=> $fullPath,
       'new_image'=> $thumbPath,
       'maintain_ration'=>true,
       'width'=>$widthThumb,
       'height'=>$heightThumb
       );

       $this->load->library('image_lib', $config);
       $this->image_lib->resize();
    }
    //--------------------------------------------------------------------------
}
?>
